package br.ucb.cesbdao.persistencia.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import br.ucb.cesbdao.persistencia.AbstractConnectionDAO;
import br.ucb.cesbdao.persistencia.factory.FactoryConnection;
import br.ucb.cesbdao.relatoriosDTO.TipoAtendeDTO;

public class TipoAtendimentoDoAtendimentoDAO extends AbstractConnectionDAO{

	private static final String sqlDelbyAtendimento = "DELETE FROM daoAtende_tipoAtende WHERE idAtendimento = ?;";
	private static final String sqlDelbyTipo = "DELETE FROM daoAtende_tipoAtende WHERE idTipoAtende = ?;";
	private static final String sqlDell = "DELETE FROM daoAtende_tipoAtende WHERE idAtendimento = ? and idTipoAtende = ?;";
	private static final String sqlAdd = "INSERT INTO daoAtende_tipoAtende VALUES(?,?);";
	private static final String sqlByIds = "SELECT *FROM daoAtende_tipoAtende WHERE idTipoAtende = ? and idAtendimento = ?;";
	private static final String sqlTpAtendeByPeriodo = 	"SELECT count(*), TP.descricao, A.data "+
														"FROM daotipoatendimento TP,daoatende_tipoAtende AT,daoatendimento A "+
														"WHERE TP.idTipoAtende = AT.idTipoAtende and "+
														"AT.idAtendimento = A.idAtendimento and "+
														"A.data >= ? and "+
														"A.data <= ? "+
														"group by DATE_FORMAT(A.data,'%c'),TP.descricao;";
	
	
	public TipoAtendimentoDoAtendimentoDAO(Connection conexao) {
		super(conexao);
	}


	public  void DellByIdAtendimento(int idAtendimento) throws SQLException, ClassNotFoundException {
		PreparedStatement stm = getConexao().prepareStatement(sqlDelbyAtendimento);
		stm.setInt(1,idAtendimento);
		stm.execute();
		FactoryConnection.getInstance().closeConexao(stm);
	}


	public  void add(int idAtendimento, int idTipoAtende) throws SQLException, ClassNotFoundException {
		
		PreparedStatement stm = getConexao().prepareStatement(sqlAdd);
		stm.setInt(1,idAtendimento);
		stm.setInt(2,idTipoAtende);
		stm.execute();
		FactoryConnection.getInstance().closeConexao(stm);

	}


	public  boolean getByIdAtend_idTipo(int idAtendimento, int idTipoAtende) throws SQLException, ClassNotFoundException {
		PreparedStatement stm = getConexao().prepareStatement(sqlByIds);
		stm.setInt(1,idTipoAtende);
		stm.setInt(2,idAtendimento);
		ResultSet rs = stm.executeQuery();
		if(rs.next()){
			FactoryConnection.getInstance().closeConexao(stm, rs);
			return true;
		}
		FactoryConnection.getInstance().closeConexao(stm, rs);
		return false;

	}


	public  void dell(int idAtendimento, int idTipoAtende) throws SQLException, ClassNotFoundException {
		
		PreparedStatement stm = getConexao().prepareStatement(sqlDell);
		stm.setInt(1,idAtendimento);
		stm.setInt(2,idTipoAtende);
		stm.execute();
		FactoryConnection.getInstance().closeConexao(stm);

	}


	public  void DellByIdTipo(int idTipoAtende) throws SQLException, ClassNotFoundException {
		
		PreparedStatement stm = getConexao().prepareStatement(sqlDelbyTipo);
		stm.setInt(1,idTipoAtende);
		stm.execute();
		FactoryConnection.getInstance().closeConexao(stm);
	}


	public  ArrayList<TipoAtendeDTO> getByPeriodo(Date dataInicio,
			Date dataFim) throws ClassNotFoundException, SQLException {
		ArrayList<TipoAtendeDTO> tipoAtendeTO = new ArrayList<TipoAtendeDTO>();
		
		PreparedStatement stm = getConexao().prepareStatement(sqlTpAtendeByPeriodo);
		stm.setDate(1, new java.sql.Date(dataInicio.getTime()));
		stm.setDate(2, new java.sql.Date(dataFim.getTime()));
		ResultSet rs = stm.executeQuery();
		while(rs.next()){
			tipoAtendeTO.add(new TipoAtendeDTO(rs.getInt(1),rs.getString(2),rs.getString(3)));
		}

		return tipoAtendeTO;
	}






}
